{
 "cells": [
  {
   "cell_type": "markdown",
   "source": [
    "# 第5章 数据整合和数据清洗\n",
    "## 5.1　数据整合\n",
    "### 5.1.1 行列操作\n",
    "#### 1. 单列"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true,
    "ExecuteTime": {
     "start_time": "2024-06-11T17:10:56.584502Z",
     "end_time": "2024-06-11T17:10:56.599556Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "          a         b         c         d         e\n0  0.479644 -1.151291  0.713526 -1.109420 -0.389155\n1 -0.216636 -0.758405 -0.032673  0.015908 -0.399717\n2 -0.261881  0.508490  0.651563  0.682311 -0.815901\n3 -0.225676  0.545143 -0.197095 -1.193298  2.225186",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>a</th>\n      <th>b</th>\n      <th>c</th>\n      <th>d</th>\n      <th>e</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>0.479644</td>\n      <td>-1.151291</td>\n      <td>0.713526</td>\n      <td>-1.109420</td>\n      <td>-0.389155</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>-0.216636</td>\n      <td>-0.758405</td>\n      <td>-0.032673</td>\n      <td>0.015908</td>\n      <td>-0.399717</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>-0.261881</td>\n      <td>0.508490</td>\n      <td>0.651563</td>\n      <td>0.682311</td>\n      <td>-0.815901</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>-0.225676</td>\n      <td>0.545143</td>\n      <td>-0.197095</td>\n      <td>-1.193298</td>\n      <td>2.225186</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "sample = pd.DataFrame(np.random.randn(4, 5),\n",
    "                      columns=['a', 'b', 'c', 'd', 'e'])\n",
    "sample"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "outputs": [
    {
     "data": {
      "text/plain": "0    0.479644\n1   -0.216636\n2   -0.261881\n3   -0.225676\nName: a, dtype: float64"
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample['a']"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T17:10:56.598553Z",
     "end_time": "2024-06-11T17:10:56.608169Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "outputs": [
    {
     "data": {
      "text/plain": "          a\n0  0.479644\n1 -0.216636\n2 -0.261881\n3 -0.225676",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>a</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>0.479644</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>-0.216636</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>-0.261881</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>-0.225676</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample[['a']]"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T17:10:56.607170Z",
     "end_time": "2024-06-11T17:10:56.639504Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "#### 2. 选择多行和多列"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "outputs": [
    {
     "data": {
      "text/plain": "          a         b         c\n0  0.479644 -1.151291  0.713526\n1 -0.216636 -0.758405 -0.032673\n2 -0.261881  0.508490  0.651563",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>a</th>\n      <th>b</th>\n      <th>c</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>0.479644</td>\n      <td>-1.151291</td>\n      <td>0.713526</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>-0.216636</td>\n      <td>-0.758405</td>\n      <td>-0.032673</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>-0.261881</td>\n      <td>0.508490</td>\n      <td>0.651563</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample.loc[[0, 1, 2], ['a', 'b', 'c']]"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T17:10:56.624590Z",
     "end_time": "2024-06-11T17:10:56.663171Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "outputs": [
    {
     "data": {
      "text/plain": "          a         b         c\n0  0.479644 -1.151291  0.713526\n1 -0.216636 -0.758405 -0.032673\n2 -0.261881  0.508490  0.651563",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>a</th>\n      <th>b</th>\n      <th>c</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>0.479644</td>\n      <td>-1.151291</td>\n      <td>0.713526</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>-0.216636</td>\n      <td>-0.758405</td>\n      <td>-0.032673</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>-0.261881</td>\n      <td>0.508490</td>\n      <td>0.651563</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample.iloc[[0, 1, 2], [0, 1, 2]]"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T17:10:56.631344Z",
     "end_time": "2024-06-11T17:10:56.749455Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "#### 3. 创建、删除列"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "outputs": [
    {
     "data": {
      "text/plain": "          a         b         c         d         e  new_col1\n0  0.479644 -1.151291  0.713526 -1.109420 -0.389155  1.630934\n1 -0.216636 -0.758405 -0.032673  0.015908 -0.399717  0.541769\n2 -0.261881  0.508490  0.651563  0.682311 -0.815901 -0.770371\n3 -0.225676  0.545143 -0.197095 -1.193298  2.225186 -0.770819",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>a</th>\n      <th>b</th>\n      <th>c</th>\n      <th>d</th>\n      <th>e</th>\n      <th>new_col1</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>0.479644</td>\n      <td>-1.151291</td>\n      <td>0.713526</td>\n      <td>-1.109420</td>\n      <td>-0.389155</td>\n      <td>1.630934</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>-0.216636</td>\n      <td>-0.758405</td>\n      <td>-0.032673</td>\n      <td>0.015908</td>\n      <td>-0.399717</td>\n      <td>0.541769</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>-0.261881</td>\n      <td>0.508490</td>\n      <td>0.651563</td>\n      <td>0.682311</td>\n      <td>-0.815901</td>\n      <td>-0.770371</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>-0.225676</td>\n      <td>0.545143</td>\n      <td>-0.197095</td>\n      <td>-1.193298</td>\n      <td>2.225186</td>\n      <td>-0.770819</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample['new_col1'] = sample['a'] - sample['b']\n",
    "sample"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T17:10:56.640509Z",
     "end_time": "2024-06-11T17:10:56.771505Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "outputs": [
    {
     "data": {
      "text/plain": "          a         b         c         d         e  new_col1  new_col2  \\\n0  0.479644 -1.151291  0.713526 -1.109420 -0.389155  1.630934  1.630934   \n1 -0.216636 -0.758405 -0.032673  0.015908 -0.399717  0.541769  0.541769   \n2 -0.261881  0.508490  0.651563  0.682311 -0.815901 -0.770371 -0.770371   \n3 -0.225676  0.545143 -0.197095 -1.193298  2.225186 -0.770819 -0.770819   \n\n   new_col3  \n0 -0.671647  \n1 -0.975040  \n2  0.246609  \n3  0.319468  ",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>a</th>\n      <th>b</th>\n      <th>c</th>\n      <th>d</th>\n      <th>e</th>\n      <th>new_col1</th>\n      <th>new_col2</th>\n      <th>new_col3</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>0.479644</td>\n      <td>-1.151291</td>\n      <td>0.713526</td>\n      <td>-1.109420</td>\n      <td>-0.389155</td>\n      <td>1.630934</td>\n      <td>1.630934</td>\n      <td>-0.671647</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>-0.216636</td>\n      <td>-0.758405</td>\n      <td>-0.032673</td>\n      <td>0.015908</td>\n      <td>-0.399717</td>\n      <td>0.541769</td>\n      <td>0.541769</td>\n      <td>-0.975040</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>-0.261881</td>\n      <td>0.508490</td>\n      <td>0.651563</td>\n      <td>0.682311</td>\n      <td>-0.815901</td>\n      <td>-0.770371</td>\n      <td>-0.770371</td>\n      <td>0.246609</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>-0.225676</td>\n      <td>0.545143</td>\n      <td>-0.197095</td>\n      <td>-1.193298</td>\n      <td>2.225186</td>\n      <td>-0.770819</td>\n      <td>-0.770819</td>\n      <td>0.319468</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample_new = sample.assign(new_col2=sample['a'] - sample['b'],\n",
    "                           new_col3=sample['a'] + sample['b'])\n",
    "sample_new"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T17:10:56.652859Z",
     "end_time": "2024-06-11T17:10:56.838310Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "outputs": [
    {
     "data": {
      "text/plain": "          b         c         d         e  new_col1\n0 -1.151291  0.713526 -1.109420 -0.389155  1.630934\n1 -0.758405 -0.032673  0.015908 -0.399717  0.541769\n2  0.508490  0.651563  0.682311 -0.815901 -0.770371\n3  0.545143 -0.197095 -1.193298  2.225186 -0.770819",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>b</th>\n      <th>c</th>\n      <th>d</th>\n      <th>e</th>\n      <th>new_col1</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>-1.151291</td>\n      <td>0.713526</td>\n      <td>-1.109420</td>\n      <td>-0.389155</td>\n      <td>1.630934</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>-0.758405</td>\n      <td>-0.032673</td>\n      <td>0.015908</td>\n      <td>-0.399717</td>\n      <td>0.541769</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>0.508490</td>\n      <td>0.651563</td>\n      <td>0.682311</td>\n      <td>-0.815901</td>\n      <td>-0.770371</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>0.545143</td>\n      <td>-0.197095</td>\n      <td>-1.193298</td>\n      <td>2.225186</td>\n      <td>-0.770819</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample.drop('a', axis=1)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T17:10:56.666182Z",
     "end_time": "2024-06-11T17:10:56.884106Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 5.1.2 条件查询"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "outputs": [
    {
     "data": {
      "text/plain": "    name  score  group\n0    Bob     98      1\n1  Lindy     78      1\n2   Mark     87      1\n3   Miki     77      2\n4  Sully     65      1\n5   Rose     67      2",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>name</th>\n      <th>score</th>\n      <th>group</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Bob</td>\n      <td>98</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>Lindy</td>\n      <td>78</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Mark</td>\n      <td>87</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Miki</td>\n      <td>77</td>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>Sully</td>\n      <td>65</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>Rose</td>\n      <td>67</td>\n      <td>2</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample = pd.DataFrame({'name': ['Bob', 'Lindy', 'Mark',\n",
    "                                'Miki', 'Sully', 'Rose'],\n",
    "                       'score': [98, 78, 87, 77, 65, 67],\n",
    "                       'group': [1, 1, 1, 2, 1, 2], })\n",
    "sample"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T17:10:56.676768Z",
     "end_time": "2024-06-11T17:10:56.890831Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "#### 1. 单条件"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "outputs": [
    {
     "data": {
      "text/plain": "    name  score  group\n0    Bob     98      1\n1  Lindy     78      1\n2   Mark     87      1\n3   Miki     77      2",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>name</th>\n      <th>score</th>\n      <th>group</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Bob</td>\n      <td>98</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>Lindy</td>\n      <td>78</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Mark</td>\n      <td>87</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Miki</td>\n      <td>77</td>\n      <td>2</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample[sample.score > 70]"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T17:10:56.692663Z",
     "end_time": "2024-06-11T17:10:56.890831Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "#### 2. 多条件"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "outputs": [
    {
     "data": {
      "text/plain": "    name  score  group\n0    Bob     98      1\n1  Lindy     78      1\n2   Mark     87      1",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>name</th>\n      <th>score</th>\n      <th>group</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Bob</td>\n      <td>98</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>Lindy</td>\n      <td>78</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Mark</td>\n      <td>87</td>\n      <td>1</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample[(sample.score > 70) & (sample.group == 1)]"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T17:10:56.700666Z",
     "end_time": "2024-06-11T17:10:56.891832Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "#### 3. 使用query"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "outputs": [
    {
     "data": {
      "text/plain": "    name  score  group\n0    Bob     98      1\n1  Lindy     78      1\n2   Mark     87      1",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>name</th>\n      <th>score</th>\n      <th>group</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Bob</td>\n      <td>98</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>Lindy</td>\n      <td>78</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Mark</td>\n      <td>87</td>\n      <td>1</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample.query('(group == 1) & (score > 70)')"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T17:10:56.719839Z",
     "end_time": "2024-06-11T17:10:56.892833Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "#### 4. 其他"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "outputs": [
    {
     "data": {
      "text/plain": "    name  score  group\n1  Lindy     78      1\n3   Miki     77      2",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>name</th>\n      <th>score</th>\n      <th>group</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>1</th>\n      <td>Lindy</td>\n      <td>78</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Miki</td>\n      <td>77</td>\n      <td>2</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample[sample['score'].between(70, 80)]"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T17:10:56.732836Z",
     "end_time": "2024-06-11T17:10:56.892833Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "outputs": [
    {
     "data": {
      "text/plain": "    name  score  group\n0    Bob     98      1\n1  Lindy     78      1",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>name</th>\n      <th>score</th>\n      <th>group</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Bob</td>\n      <td>98</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>Lindy</td>\n      <td>78</td>\n      <td>1</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample[sample['name'].isin(['Bob', 'Lindy'])]"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T17:10:56.746459Z",
     "end_time": "2024-06-11T17:10:56.892833Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "outputs": [
    {
     "data": {
      "text/plain": "   name  score  group\n2  Mark     87      1\n3  Miki     77      2",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>name</th>\n      <th>score</th>\n      <th>group</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>2</th>\n      <td>Mark</td>\n      <td>87</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Miki</td>\n      <td>77</td>\n      <td>2</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample[sample['name'].str.contains('[M]+')]"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T17:10:56.755982Z",
     "end_time": "2024-06-11T17:10:56.892833Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 5.1.3 横向连接"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "outputs": [],
   "source": [
    "df1 = pd.DataFrame({'id': [1, 2, 3],\n",
    "                    'col1': ['a', 'b', 'c']})\n",
    "df2 = pd.DataFrame({'id': [4, 3],\n",
    "                    'col2': ['d', 'e']})"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T17:10:56.770155Z",
     "end_time": "2024-06-11T17:10:56.892833Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "#### 1. 内连接"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "outputs": [
    {
     "data": {
      "text/plain": "   id col1 col2\n0   3    c    e",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>id</th>\n      <th>col1</th>\n      <th>col2</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>3</td>\n      <td>c</td>\n      <td>e</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.merge(df2, how='inner', left_on='id', right_on='id')"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T17:10:56.776503Z",
     "end_time": "2024-06-11T17:10:56.893830Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "#### 2. 外连接"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "outputs": [
    {
     "data": {
      "text/plain": "   id col1 col2\n0   1    a  NaN\n1   2    b  NaN\n2   3    c    e",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>id</th>\n      <th>col1</th>\n      <th>col2</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>1</td>\n      <td>a</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>2</td>\n      <td>b</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>3</td>\n      <td>c</td>\n      <td>e</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.merge(df2, how='left', on='id')"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T17:10:56.788047Z",
     "end_time": "2024-06-11T17:10:56.956810Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "#### 3. 行索引连接"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "outputs": [
    {
     "data": {
      "text/plain": "   id1 col1  id2 col2\n1    1    a    1   aa\n2    2    b    3   cc\n3    3    c    2   bb",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>id1</th>\n      <th>col1</th>\n      <th>id2</th>\n      <th>col2</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>1</th>\n      <td>1</td>\n      <td>a</td>\n      <td>1</td>\n      <td>aa</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2</td>\n      <td>b</td>\n      <td>3</td>\n      <td>cc</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>3</td>\n      <td>c</td>\n      <td>2</td>\n      <td>bb</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.DataFrame({'id1': [1, 2, 3],\n",
    "                    'col1': ['a', 'b', 'c']},\n",
    "                   index=[1, 2, 3])\n",
    "df2 = pd.DataFrame({'id2': [1, 2, 3],\n",
    "                    'col2': ['aa', 'bb', 'cc']},\n",
    "                   index=[1, 3, 2])\n",
    "pd.concat([df1, df2], axis=1)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T17:10:56.800510Z",
     "end_time": "2024-06-11T17:10:57.033951Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 5.1.4 纵向合并"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "outputs": [
    {
     "data": {
      "text/plain": "    id col\n0    1   a\n1    1   a\n2    1   b\n3    2   c\n4    3   v\n5    4   e\n6    6   q\n7    1   x\n8    2   y\n9    3   z\n10   3   v\n11   5   w",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>id</th>\n      <th>col</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>1</td>\n      <td>a</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>1</td>\n      <td>a</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>1</td>\n      <td>b</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>2</td>\n      <td>c</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>3</td>\n      <td>v</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>4</td>\n      <td>e</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>6</td>\n      <td>q</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>1</td>\n      <td>x</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>2</td>\n      <td>y</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>3</td>\n      <td>z</td>\n    </tr>\n    <tr>\n      <th>10</th>\n      <td>3</td>\n      <td>v</td>\n    </tr>\n    <tr>\n      <th>11</th>\n      <td>5</td>\n      <td>w</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.DataFrame({'id': [1, 1, 1, 2, 3, 4, 6],\n",
    "                    'col': ['a', 'a', 'b', 'c', 'v', 'e', 'q']})\n",
    "df2 = pd.DataFrame({'id': [1, 2, 3, 3, 5],\n",
    "                    'col': ['x', 'y', 'z', 'v', 'w']})\n",
    "pd.concat([df1, df2], ignore_index=True, axis=0)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T17:10:56.820080Z",
     "end_time": "2024-06-11T17:10:57.034953Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "outputs": [
    {
     "data": {
      "text/plain": "    id col\n0    1   a\n2    1   b\n3    2   c\n4    3   v\n5    4   e\n6    6   q\n7    1   x\n8    2   y\n9    3   z\n11   5   w",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>id</th>\n      <th>col</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>1</td>\n      <td>a</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>1</td>\n      <td>b</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>2</td>\n      <td>c</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>3</td>\n      <td>v</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>4</td>\n      <td>e</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>6</td>\n      <td>q</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>1</td>\n      <td>x</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>2</td>\n      <td>y</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>3</td>\n      <td>z</td>\n    </tr>\n    <tr>\n      <th>11</th>\n      <td>5</td>\n      <td>w</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([df1, df2], ignore_index=True).drop_duplicates()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T17:10:56.833310Z",
     "end_time": "2024-06-11T17:10:57.034953Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "outputs": [
    {
     "data": {
      "text/plain": "    id  col new_col\n0    1    a     NaN\n2    1    b     NaN\n3    2    c     NaN\n4    3    v     NaN\n5    4    e     NaN\n6    6    q     NaN\n7    1  NaN       a\n9    1  NaN       b\n10   2  NaN       c\n11   3  NaN       v\n12   4  NaN       e\n13   6  NaN       q",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>id</th>\n      <th>col</th>\n      <th>new_col</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>1</td>\n      <td>a</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>1</td>\n      <td>b</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>2</td>\n      <td>c</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>3</td>\n      <td>v</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>4</td>\n      <td>e</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>6</td>\n      <td>q</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>1</td>\n      <td>NaN</td>\n      <td>a</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>1</td>\n      <td>NaN</td>\n      <td>b</td>\n    </tr>\n    <tr>\n      <th>10</th>\n      <td>2</td>\n      <td>NaN</td>\n      <td>c</td>\n    </tr>\n    <tr>\n      <th>11</th>\n      <td>3</td>\n      <td>NaN</td>\n      <td>v</td>\n    </tr>\n    <tr>\n      <th>12</th>\n      <td>4</td>\n      <td>NaN</td>\n      <td>e</td>\n    </tr>\n    <tr>\n      <th>13</th>\n      <td>6</td>\n      <td>NaN</td>\n      <td>q</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3 = df1.rename(columns={'col': 'new_col'})\n",
    "pd.concat([df1, df3], ignore_index=True).drop_duplicates()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T17:10:56.845057Z",
     "end_time": "2024-06-11T17:10:57.224033Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 5.1.5 排序\n",
    "#### 1. 排序"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "outputs": [
    {
     "data": {
      "text/plain": "    name  score  group\n0    Bob   98.0      1\n1  Lindy   78.0      1\n2   Mark   87.0      1\n3   Miki   77.0      2\n4  Sully   77.0      1\n5   Rose    NaN      2",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>name</th>\n      <th>score</th>\n      <th>group</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Bob</td>\n      <td>98.0</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>Lindy</td>\n      <td>78.0</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Mark</td>\n      <td>87.0</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Miki</td>\n      <td>77.0</td>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>Sully</td>\n      <td>77.0</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>Rose</td>\n      <td>NaN</td>\n      <td>2</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample = pd.DataFrame({'name': ['Bob', 'Lindy', 'Mark', 'Miki', 'Sully', 'Rose'], 'score': [98, 78, 87, 77, 77, np.nan],\n",
    "                       'group': [1, 1, 1, 2, 1, 2], })\n",
    "sample"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T17:10:56.869884Z",
     "end_time": "2024-06-11T17:10:57.232011Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "outputs": [
    {
     "data": {
      "text/plain": "    name  score  group\n0    Bob   98.0      1\n2   Mark   87.0      1\n1  Lindy   78.0      1\n3   Miki   77.0      2\n4  Sully   77.0      1\n5   Rose    NaN      2",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>name</th>\n      <th>score</th>\n      <th>group</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Bob</td>\n      <td>98.0</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Mark</td>\n      <td>87.0</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>Lindy</td>\n      <td>78.0</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Miki</td>\n      <td>77.0</td>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>Sully</td>\n      <td>77.0</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>Rose</td>\n      <td>NaN</td>\n      <td>2</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample.sort_values('score', ascending=False, na_position='last')"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T17:10:56.882105Z",
     "end_time": "2024-06-11T17:10:57.244015Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "outputs": [
    {
     "data": {
      "text/plain": "    name  score  group\n4  Sully   77.0      1\n1  Lindy   78.0      1\n2   Mark   87.0      1\n0    Bob   98.0      1\n3   Miki   77.0      2\n5   Rose    NaN      2",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>name</th>\n      <th>score</th>\n      <th>group</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>4</th>\n      <td>Sully</td>\n      <td>77.0</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>Lindy</td>\n      <td>78.0</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Mark</td>\n      <td>87.0</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>0</th>\n      <td>Bob</td>\n      <td>98.0</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Miki</td>\n      <td>77.0</td>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>Rose</td>\n      <td>NaN</td>\n      <td>2</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample.sort_values(['group', 'score'])"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T17:10:56.893830Z",
     "end_time": "2024-06-11T17:10:57.286011Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 5.1.6 分组汇总"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "outputs": [
    {
     "data": {
      "text/plain": "   chinese  class  grade  math   name\n0       88      1      1  98.0    Bob\n1       78      1      1  78.0  Lindy\n2       86      1      1  87.0   Mark\n3       56      2      2  77.0   Miki\n4       77      1      2  77.0  Sully",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>chinese</th>\n      <th>class</th>\n      <th>grade</th>\n      <th>math</th>\n      <th>name</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>88</td>\n      <td>1</td>\n      <td>1</td>\n      <td>98.0</td>\n      <td>Bob</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>78</td>\n      <td>1</td>\n      <td>1</td>\n      <td>78.0</td>\n      <td>Lindy</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>86</td>\n      <td>1</td>\n      <td>1</td>\n      <td>87.0</td>\n      <td>Mark</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>56</td>\n      <td>2</td>\n      <td>2</td>\n      <td>77.0</td>\n      <td>Miki</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>77</td>\n      <td>1</td>\n      <td>2</td>\n      <td>77.0</td>\n      <td>Sully</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample = pd.read_csv('../data/sample.csv', encoding='gbk')\n",
    "sample.head()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T17:10:56.905413Z",
     "end_time": "2024-06-11T17:10:57.296018Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "outputs": [
    {
     "data": {
      "text/plain": "       math\nclass      \n1      98.0\n2      77.0",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>math</th>\n    </tr>\n    <tr>\n      <th>class</th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>1</th>\n      <td>98.0</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>77.0</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample.groupby('class')[['math']].max()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T17:10:56.918471Z",
     "end_time": "2024-06-11T17:10:57.307013Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "outputs": [
    {
     "data": {
      "text/plain": "                  math\ngrade class           \n1     1      87.666667\n2     1      77.000000\n      2      77.000000",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th></th>\n      <th>math</th>\n    </tr>\n    <tr>\n      <th>grade</th>\n      <th>class</th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>1</th>\n      <th>1</th>\n      <td>87.666667</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">2</th>\n      <th>1</th>\n      <td>77.000000</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>77.000000</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample.groupby(['grade', 'class'])[['math']].mean()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T17:10:56.926676Z",
     "end_time": "2024-06-11T17:10:57.308013Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "outputs": [
    {
     "data": {
      "text/plain": "            math    chinese\ngrade                      \n1      87.666667  84.000000\n2      77.000000  62.333333",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>math</th>\n      <th>chinese</th>\n    </tr>\n    <tr>\n      <th>grade</th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>1</th>\n      <td>87.666667</td>\n      <td>84.000000</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>77.000000</td>\n      <td>62.333333</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample.groupby(['grade'])[['math', 'chinese']].mean()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T17:10:56.938081Z",
     "end_time": "2024-06-11T17:10:57.308013Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "outputs": [
    {
     "data": {
      "text/plain": "       mean   min   max\nclass                  \n1      85.0  77.0  98.0\n2      77.0  77.0  77.0",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>mean</th>\n      <th>min</th>\n      <th>max</th>\n    </tr>\n    <tr>\n      <th>class</th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>1</th>\n      <td>85.0</td>\n      <td>77.0</td>\n      <td>98.0</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>77.0</td>\n      <td>77.0</td>\n      <td>77.0</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample.groupby('class')['math'].agg(['mean', 'min', 'max'])"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T17:10:56.948631Z",
     "end_time": "2024-06-11T17:10:57.308013Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "outputs": [
    {
     "data": {
      "text/plain": "             math       chinese    \n              min   max     min max\ngrade class                        \n1     1      78.0  98.0      78  88\n2     1      77.0  77.0      77  77\n      2      77.0  77.0      54  56",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead tr th {\n        text-align: left;\n    }\n\n    .dataframe thead tr:last-of-type th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr>\n      <th></th>\n      <th></th>\n      <th colspan=\"2\" halign=\"left\">math</th>\n      <th colspan=\"2\" halign=\"left\">chinese</th>\n    </tr>\n    <tr>\n      <th></th>\n      <th></th>\n      <th>min</th>\n      <th>max</th>\n      <th>min</th>\n      <th>max</th>\n    </tr>\n    <tr>\n      <th>grade</th>\n      <th>class</th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>1</th>\n      <th>1</th>\n      <td>78.0</td>\n      <td>98.0</td>\n      <td>78</td>\n      <td>88</td>\n    </tr>\n    <tr>\n      <th rowspan=\"2\" valign=\"top\">2</th>\n      <th>1</th>\n      <td>77.0</td>\n      <td>77.0</td>\n      <td>77</td>\n      <td>77</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>77.0</td>\n      <td>77.0</td>\n      <td>54</td>\n      <td>56</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = sample.groupby(['grade', 'class'])[['math', 'chinese']].agg(['min', 'max'])\n",
    "df"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T17:10:56.958814Z",
     "end_time": "2024-06-11T17:10:57.346019Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 5.1.7 拆分、堆叠列"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "outputs": [],
   "source": [
    "table = pd.DataFrame({'cust_id': [10001, 10001, 10002, 10002, 10003],\n",
    "                      'type': ['Normal', 'Special_offer', \\\n",
    "                               'Normal', 'Special_offer', 'Special_offer'],\n",
    "                      'Monetary': [3608, 420, 1894, 3503, 4567]})"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T17:10:56.976092Z",
     "end_time": "2024-06-11T17:10:57.346019Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "outputs": [
    {
     "data": {
      "text/plain": "type     Normal  Special_offer\ncust_id                       \n10001    3608.0          420.0\n10002    1894.0         3503.0\n10003       NaN         4567.0",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th>type</th>\n      <th>Normal</th>\n      <th>Special_offer</th>\n    </tr>\n    <tr>\n      <th>cust_id</th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>10001</th>\n      <td>3608.0</td>\n      <td>420.0</td>\n    </tr>\n    <tr>\n      <th>10002</th>\n      <td>1894.0</td>\n      <td>3503.0</td>\n    </tr>\n    <tr>\n      <th>10003</th>\n      <td>NaN</td>\n      <td>4567.0</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(table, index='cust_id', columns='type', values='Monetary')"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T17:10:56.980092Z",
     "end_time": "2024-06-11T17:10:57.346019Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "outputs": [
    {
     "data": {
      "text/plain": "type     Normal  Special_offer\ncust_id                       \n10001      3608            420\n10002      1894           3503\n10003         0           4567",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th>type</th>\n      <th>Normal</th>\n      <th>Special_offer</th>\n    </tr>\n    <tr>\n      <th>cust_id</th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>10001</th>\n      <td>3608</td>\n      <td>420</td>\n    </tr>\n    <tr>\n      <th>10002</th>\n      <td>1894</td>\n      <td>3503</td>\n    </tr>\n    <tr>\n      <th>10003</th>\n      <td>0</td>\n      <td>4567</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(table, index='cust_id', columns='type', values='Monetary', fill_value=0, aggfunc='sum')"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T17:10:56.995907Z",
     "end_time": "2024-06-11T17:10:57.383033Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\HP\\AppData\\Local\\Temp\\ipykernel_22016\\3280458140.py:1: FutureWarning: The provided callable <function sum at 0x000001F67FA33740> is currently using DataFrameGroupBy.sum. In a future version of pandas, the provided callable will be used directly. To keep current behavior pass the string \"sum\" instead.\n",
      "  table1 = pd.pivot_table(table, index='cust_id',\n"
     ]
    },
    {
     "data": {
      "text/plain": "type  cust_id  Normal  Special_offer\n0       10001    3608            420\n1       10002    1894           3503\n2       10003       0           4567",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th>type</th>\n      <th>cust_id</th>\n      <th>Normal</th>\n      <th>Special_offer</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>10001</td>\n      <td>3608</td>\n      <td>420</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>10002</td>\n      <td>1894</td>\n      <td>3503</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>10003</td>\n      <td>0</td>\n      <td>4567</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table1 = pd.pivot_table(table, index='cust_id',\n",
    "                        columns='type',\n",
    "                        values='Monetary',\n",
    "                        fill_value=0,\n",
    "                        aggfunc=np.sum).reset_index()\n",
    "table1"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T17:10:57.007135Z",
     "end_time": "2024-06-11T17:10:57.413033Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "outputs": [
    {
     "data": {
      "text/plain": "   cust_id           TYPE  Monetary\n0    10001         Normal      3608\n1    10002         Normal      1894\n2    10003         Normal         0\n3    10001  Special_offer       420\n4    10002  Special_offer      3503\n5    10003  Special_offer      4567",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>cust_id</th>\n      <th>TYPE</th>\n      <th>Monetary</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>10001</td>\n      <td>Normal</td>\n      <td>3608</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>10002</td>\n      <td>Normal</td>\n      <td>1894</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>10003</td>\n      <td>Normal</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>10001</td>\n      <td>Special_offer</td>\n      <td>420</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>10002</td>\n      <td>Special_offer</td>\n      <td>3503</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>10003</td>\n      <td>Special_offer</td>\n      <td>4567</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.melt(table1,\n",
    "        id_vars='cust_id',\n",
    "        value_vars=['Normal', 'Special_offer'],\n",
    "        value_name='Monetary',\n",
    "        var_name='TYPE')"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T17:10:57.027960Z",
     "end_time": "2024-06-11T17:10:57.414033Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 5.1.8 赋值与条件赋值\n",
    "#### 1. 赋值"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "outputs": [
    {
     "data": {
      "text/plain": "0    99.0\n1    78.0\n2     NaN\n3    77.0\n4    77.0\n5     NaN\nName: score, dtype: float64"
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample = pd.DataFrame({'name': ['Bob', 'Lindy', 'Mark',\n",
    "                                'Miki', 'Sully', 'Rose'],\n",
    "                       'score': [99, 78, 999, 77, 77, np.nan],\n",
    "                       'group': [1, 1, 1, 2, 1, 2], })\n",
    "sample.score.replace(999, np.nan)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T17:10:57.043355Z",
     "end_time": "2024-06-11T17:10:57.461018Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "outputs": [
    {
     "data": {
      "text/plain": "    name  score  group\n0    NaN   99.0      1\n1  Lindy   78.0      1\n2   Mark    NaN      1\n3   Miki   77.0      2\n4  Sully   77.0      1\n5   Rose    NaN      2",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>name</th>\n      <th>score</th>\n      <th>group</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>NaN</td>\n      <td>99.0</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>Lindy</td>\n      <td>78.0</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Mark</td>\n      <td>NaN</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Miki</td>\n      <td>77.0</td>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>Sully</td>\n      <td>77.0</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>Rose</td>\n      <td>NaN</td>\n      <td>2</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample.replace({'score': {999: np.nan},\n",
    "                'name': {'Bob': np.nan}})"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T17:10:57.053847Z",
     "end_time": "2024-06-11T17:10:57.556033Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "#### 2. 条件赋值"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "outputs": [
    {
     "data": {
      "text/plain": "0    class1\n1    class1\n2    class1\n3    class2\n4    class1\n5    class2\ndtype: object"
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def transform(row):\n",
    "    if row['group'] == 1:\n",
    "        return ('class1')\n",
    "    elif row['group'] == 2:\n",
    "        return ('class2')\n",
    "\n",
    "\n",
    "sample.apply(transform, axis=1)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T17:10:57.064760Z",
     "end_time": "2024-06-11T17:10:57.635494Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "outputs": [
    {
     "data": {
      "text/plain": "    name  score  group class_n\n0    Bob   99.0      1  class1\n1  Lindy   78.0      1  class1\n2   Mark  999.0      1  class1\n3   Miki   77.0      2  class2\n4  Sully   77.0      1  class1\n5   Rose    NaN      2  class2",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>name</th>\n      <th>score</th>\n      <th>group</th>\n      <th>class_n</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Bob</td>\n      <td>99.0</td>\n      <td>1</td>\n      <td>class1</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>Lindy</td>\n      <td>78.0</td>\n      <td>1</td>\n      <td>class1</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Mark</td>\n      <td>999.0</td>\n      <td>1</td>\n      <td>class1</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Miki</td>\n      <td>77.0</td>\n      <td>2</td>\n      <td>class2</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>Sully</td>\n      <td>77.0</td>\n      <td>1</td>\n      <td>class1</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>Rose</td>\n      <td>NaN</td>\n      <td>2</td>\n      <td>class2</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample.assign(class_n=sample.apply(transform, axis=1))"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T17:10:57.073290Z",
     "end_time": "2024-06-11T17:10:57.721805Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "outputs": [
    {
     "data": {
      "text/plain": "    name  score  group class_n\n0    Bob   99.0      1  class1\n1  Lindy   78.0      1  class1\n2   Mark  999.0      1  class1\n3   Miki   77.0      2  class2\n4  Sully   77.0      1  class1\n5   Rose    NaN      2  class2",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>name</th>\n      <th>score</th>\n      <th>group</th>\n      <th>class_n</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Bob</td>\n      <td>99.0</td>\n      <td>1</td>\n      <td>class1</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>Lindy</td>\n      <td>78.0</td>\n      <td>1</td>\n      <td>class1</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Mark</td>\n      <td>999.0</td>\n      <td>1</td>\n      <td>class1</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Miki</td>\n      <td>77.0</td>\n      <td>2</td>\n      <td>class2</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>Sully</td>\n      <td>77.0</td>\n      <td>1</td>\n      <td>class1</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>Rose</td>\n      <td>NaN</td>\n      <td>2</td>\n      <td>class2</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample = sample.copy()\n",
    "sample.loc[sample.group == 1, 'class_n'] = 'class1'\n",
    "sample.loc[sample.group == 2, 'class_n'] = 'class2'\n",
    "sample"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T17:10:57.084137Z",
     "end_time": "2024-06-11T17:10:57.840803Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "outputs": [],
   "source": [],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-11T17:10:57.097374Z",
     "end_time": "2024-06-11T17:10:57.904803Z"
    }
   }
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}
